package com.atguigu.gmall.gmallsugar.mapper;

import bean.C3Stats;
import bean.TmStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * @Author lizhenchao@atguigu.cn
 * @Date 2021/8/9 9:22
 */
public interface ProductMapper {
    // 计算当天或者指定日志总的销售额
    @Select("SELECT sum(order_amount)\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}")
    BigDecimal getGMV(@Param("date") int date);  //  20210809
    
    @Select("SELECT\n" +
        "    tm_name,\n" +
        "    sum(order_amount) order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY tm_name\n" +
        "order by order_amount desc " +
        "LIMIT #{limit}")
    List<TmStats> getGMVByTM(@Param("date") int date, @Param("limit") int limit);
    
    @Select("SELECT\n" +
        "    category3_name,\n" +
        "    sum(order_amount) order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY category3_name\n" +
        "order by order_amount desc " +
        "LIMIT #{limit}")
    List<C3Stats> getGMVByC3(@Param("date") int date, @Param("limit") int limit);
    
    
    @Select("SELECT\n" +
        "    spu_name,\n" +
        "    sum(order_amount) order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY spu_name\n" +
        "order by order_amount desc " +
        "LIMIT #{limit}")
    List<Map<String, Object>> getGMVBySpu(@Param("date") int date, @Param("limit") int limit);
    
    
    
}
